﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QuanLyHocPhi.BLL;
using QuanLyHocPhi.Model;
using QuanLyHocPhi.GUI.DuLieu;
using System.IO;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace QuanLyHocPhi.GUI.TacNghiep
{
    public partial class FormThongKeTheoLop : Form
    {
        private string fileName = string.Format("{0}\\TEMPLATES\\MauThongKeDSTheoLop.xls",
        Application.StartupPath);
        private string fileName2 = string.Format("{0}\\TEMPLATES\\MauThongKeTheoLop.xls",
        Application.StartupPath);
        bool lockUpdate = false;
        public FormThongKeTheoLop()
        {
            InitializeComponent();

            #region Cấu hình lưới lớp gốc
            colMaLop.DataPropertyName = "MaLopGoc";
            colTenLop.DataPropertyName = "TenLopGoc";
            gridLopGoc.AutoGenerateColumns = false;
            gridLopGoc.AllowDrop = false;
            gridLopGoc.AllowUserToAddRows = false;
            gridLopGoc.AllowUserToDeleteRows = false;
            #endregion

            #region Cấu hình lưới danh sách sinh viên
            colHK1.DataPropertyName = "HK1";
            colHK2.DataPropertyName = "HK2";
            colHK3.DataPropertyName = "HK3";
            colHK4.DataPropertyName = "HK4";
            colHK5.DataPropertyName = "HK5";
            colHK6.DataPropertyName = "HK6";
            colHK7.DataPropertyName = "HK7";
            colHK8.DataPropertyName = "HK8";
            colHK9.DataPropertyName = "HK9";
            colHK10.DataPropertyName = "HK10";
            colMSV.DataPropertyName = "MaSinhVien";
            colHoDem.DataPropertyName = "HoDem";
            colTen.DataPropertyName = "Ten";
            gridDanhSachSinhVien.AutoGenerateColumns = false;
            gridDanhSachSinhVien.AllowDrop = false;
            gridDanhSachSinhVien.AllowUserToAddRows = false;
            gridDanhSachSinhVien.AllowUserToDeleteRows = false;
            #endregion

            ShowHideColumn();

            LoadLoaiHinh();
        }

        private void InitComboSuggestion(ComboBox cmb, object datasource, string displayMember, string valueMember, string[] rangeDisplay)
        {
            cmb.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cmb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cmb.DataSource = datasource;
            if (displayMember != null)
                cmb.DisplayMember = displayMember;
            if (valueMember != null)
                cmb.ValueMember = valueMember;
            // Xác định AutoCompleteCustomeSource
            var autoCompletSet = new AutoCompleteStringCollection();
            autoCompletSet.AddRange(rangeDisplay);
            cmb.AutoCompleteCustomSource = autoCompletSet;

            // Các tinh chỉnh nâng cao
            cmb.Leave += (oSender, oE) =>
            {
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };

            cmb.KeyDown += (oSender, oE) =>
            {
                if (oE.KeyCode != Keys.Enter)
                    return;
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };
        }

        private void ShowHideColumn()
        {
            colHK1.Visible = colHK2.Visible = colHK3.Visible = colHK4.Visible =
            colHK5.Visible = colHK6.Visible = colHK7.Visible = colHK8.Visible =
            colHK9.Visible = colHK10.Visible = false;
            if (ndSoHocKy.Value >= 1)
                colHK1.Visible = true;
            if (ndSoHocKy.Value >= 2)
                colHK2.Visible = true;
            if (ndSoHocKy.Value >= 3)
                colHK3.Visible = true;
            if (ndSoHocKy.Value >= 4)
                colHK4.Visible = true;
            if (ndSoHocKy.Value >= 5)
                colHK5.Visible = true;
            if (ndSoHocKy.Value >= 6)
                colHK6.Visible = true;
            if (ndSoHocKy.Value >= 7)
                colHK7.Visible = true;
            if (ndSoHocKy.Value >= 8)
                colHK8.Visible = true;
            if (ndSoHocKy.Value >= 9)
                colHK9.Visible = true;
            if (ndSoHocKy.Value >= 10)
                colHK10.Visible = true;
        }

        private void LoadLoaiHinh()
        {
            lockUpdate = true;
            var ds = BizLoaiHinhDaoTao.GetListLoaiHinh();
            InitComboSuggestion(cmbLoaiHinh, ds, "TenLoaiHinh", "MaLoaiHinh", ds.Select(e => e.TenLoaiHinh).ToArray());
            //cmbLoaiHinh.DataSource = ds;
            //cmbLoaiHinh.DisplayMember = "TenLoaiHinh";
            //cmbLoaiHinh.ValueMember = "MaLoaiHinh";
            lockUpdate = false;
            LoadNamTuyenSinh();
        }

        private void LoadNamTuyenSinh()
        {
            lockUpdate = true;
            cmbNamTuyenSinh.DataSource = null;
            if ((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao) != null)
            {
                var ds = BizLoaiHinhDaoTao.GetList_NamTuyenSinh((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).MaLoaiHinh);
                InitComboSuggestion(cmbNamTuyenSinh, ds, null, null, ds.ToArray());
                //cmbNamTuyenSinh.DataSource = ds;
                lockUpdate = false;
                LoadLopGoc();
            }
        }

        private void LoadLopGoc()
        {
            lockUpdate = true;
            bdsLop.DataSource = null;
            gridLopGoc.DataSource = bdsLop;
            var ds = BizLopGoc.GetListLopGoc((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).MaLoaiHinh, cmbNamTuyenSinh.SelectedItem as string);
            bdsLop.DataSource = ds;
            gridLopGoc.DataSource = bdsLop;
            lbSoLop.Text = ds.Count.ToString() + " lớp";
            lbSoSinhVien.Text = "0 sinh viên";
            lockUpdate = false;
            LoadDanhSachThongKeLop();
        }

        private void cmbLoaiHinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
            {
                LoadNamTuyenSinh();
            }
        }

        private void cmbNamTuyenSinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
            {
                LoadLopGoc();
            }
        }

        private void bdsLop_CurrentChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
                LoadDanhSachThongKeLop();
        }

        private void LoadDanhSachThongKeLop()
        {
            if (bdsLop.Current != null)
            {
                var selectedLop = bdsLop.Current as LopGoc;
                var ds = new List<ThongKeLop>();
                var frm = new FormProgress();
                frm.DoWork += (oSender, oE) =>
                {
                    ds = BizPhieuThu.GetThongKeTheoLop(selectedLop.MaLopGoc, rdHocPhi.Checked);
                };

                frm.RunWorkerCompleted += (oSender, oE) =>
                {
                };
                frm.StartWorking(this);
                ShowHideColumn();
                bdsDanhSachSinhVien.DataSource = ds;
                gridDanhSachSinhVien.DataSource = bdsDanhSachSinhVien;
                lbSoSinhVien.Text = ds.Count.ToString() + " sinh viên";
            }
            else
            {
                bdsDanhSachSinhVien.DataSource = null;
                gridDanhSachSinhVien.DataSource = bdsDanhSachSinhVien;
            }
        }

        private void ndSoHocKy_ValueChanged(object sender, EventArgs e)
        {
            ShowHideColumn();
            gridDanhSachSinhVien.Refresh();
        }

        private void bdsDanhSachSinhVien_DataSourceChanged(object sender, EventArgs e)
        {
            if (bdsDanhSachSinhVien.DataSource != null)
                bntXuatRaFile.Enabled = true;
            else
                bntXuatRaFile.Enabled = false;
        }

        private void bntXuatRaFile_Click(object sender, EventArgs e)
        {
            var danhSach = bdsDanhSachSinhVien.DataSource as List<ThongKeLop>;
            var soLuong = danhSach.Count;
            string tenLoaiHinh = (cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).TenLoaiHinh;
            string tenLopGoc = (bdsLop.Current as LopGoc).TenLopGoc;
            bool duLieuRong = soLuong == 0 ? true : false;
            FileStream fs = null;
            if (duLieuRong)
                MessageBox.Show("Hiện tại danh sách là rỗng", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            else
            {
                #region Tiền xử lý
                if (!File.Exists(fileName))
                {
                    if (!File.Exists(fileName2))
                    {
                        MessageBox.Show("File mẫu không tồn tại", "Lỗi");
                        return;
                    }
                    try
                    {
                        fs = new FileStream(fileName2, FileMode.Open, FileAccess.Read);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("File mẫu đang mở. Vui lòng đóng lại trước khi tiếp tục", "Lỗi");
                        return;
                    }
                }
                else
                {
                    try
                    {
                        fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("File mẫu đang mở. Vui lòng đóng lại trước khi tiếp tục", "Lỗi");
                        return;
                    }
                }
                #endregion

                #region Làm việc với file excel
                FormProgress frm = new FormProgress();
                frm.DoWork += delegate(object oSender, DoWorkEventArgs oE)
                {
                    HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                    #region Tạo style cho thông tin cs nhân
                    var titleCellStyle = templateWorkbook.CreateCellStyle();
                    titleCellStyle.Alignment = HorizontalAlignment.CENTER;
                    var titleFont = templateWorkbook.CreateFont();
                    titleFont.Boldweight = (short)FontBoldWeight.BOLD;
                    titleFont.FontHeight = 236;
                    titleFont.FontName = "Times New Roman";
                    titleCellStyle.SetFont(titleFont);
                    #endregion

                    #region Tạo các style cho phần hiển thị nội dung
                    var detailCellStyle = templateWorkbook.CreateCellStyle();
                    detailCellStyle.Alignment = HorizontalAlignment.CENTER;
                    detailCellStyle.BorderBottom = CellBorderType.DASHED;
                    detailCellStyle.BorderLeft = CellBorderType.THIN;
                    detailCellStyle.BorderRight = CellBorderType.THIN;
                    var cellHoStyle = templateWorkbook.CreateCellStyle();
                    cellHoStyle.BorderBottom = CellBorderType.DASHED;
                    cellHoStyle.BorderLeft = CellBorderType.THIN;
                    var cellTenStyle = templateWorkbook.CreateCellStyle();
                    cellTenStyle.BorderBottom = CellBorderType.DASHED;
                    cellTenStyle.BorderRight = CellBorderType.THIN;

                    var detailCellStyleEnd = templateWorkbook.CreateCellStyle();
                    detailCellStyleEnd.Alignment = HorizontalAlignment.CENTER;
                    detailCellStyleEnd.BorderBottom = CellBorderType.THIN;
                    detailCellStyleEnd.BorderLeft = CellBorderType.THIN;
                    detailCellStyleEnd.BorderRight = CellBorderType.THIN;
                    var cellHoStyleEnd = templateWorkbook.CreateCellStyle();
                    cellHoStyleEnd.BorderBottom = CellBorderType.THIN;
                    cellHoStyleEnd.BorderLeft = CellBorderType.THIN;
                    var cellTenStyleEnd = templateWorkbook.CreateCellStyle();
                    cellTenStyleEnd.BorderBottom = CellBorderType.THIN;
                    cellTenStyleEnd.BorderRight = CellBorderType.THIN;

                    var cellTongSoStyle = templateWorkbook.CreateCellStyle();
                    var fontCellTongSo = templateWorkbook.CreateFont();
                    fontCellTongSo.IsItalic = true;
                    cellTongSoStyle.SetFont(fontCellTongSo);
                    var cellBottomStyle = templateWorkbook.CreateCellStyle();
                    cellBottomStyle.Alignment = HorizontalAlignment.CENTER;
                    var fontCellBottomStyle = templateWorkbook.CreateFont();
                    fontCellBottomStyle.Boldweight = (short)FontBoldWeight.BOLD;
                    cellBottomStyle.SetFont(fontCellBottomStyle);

                    var tieuDeStyle = templateWorkbook.CreateCellStyle();
                    tieuDeStyle.Alignment = HorizontalAlignment.CENTER;
                    tieuDeStyle.BorderBottom = CellBorderType.THIN;
                    tieuDeStyle.BorderLeft = CellBorderType.THIN;
                    tieuDeStyle.BorderRight = CellBorderType.THIN;
                    tieuDeStyle.BorderTop = CellBorderType.THIN;
                    tieuDeStyle.SetFont(fontCellBottomStyle);
                    #endregion

                    #region Khởi tạo
                    var sheet = templateWorkbook.GetSheet("sheet1");
                    var indexRow = 5;
                    var row = sheet.GetRow(indexRow);
                    #endregion

                    #region Chèn phần thông tin cá nhân
                    Cell cell;
                    try
                    {
                        cell = row.CreateCell(2);
                    }
                    catch
                    {
                        cell = row.GetCell(2);
                    }
                    cell.SetCellValue(tenLoaiHinh);

                    cell.CellStyle = titleCellStyle;
                    try
                    {
                        cell = row.CreateCell(4);
                    }
                    catch
                    {
                        cell = row.GetCell(4);
                    }
                    cell.SetCellValue(tenLopGoc);
                    cell.CellStyle = titleCellStyle;

                    #endregion

                    #region Chèn tiêu đề
                    indexRow += 2;
                    row = sheet.GetRow(indexRow);
                    for (int i = 0; i < ndSoHocKy.Value; i++)
                    {
                        try
                        {
                            cell = row.CreateCell(i + 4);
                        }
                        catch
                        {
                            cell = row.GetCell(i + 4);
                        }
                        cell.SetCellValue("Học kỳ " + (i + 1).ToString());
                        cell.CellStyle = tieuDeStyle;
                    }
                    #endregion

                    #region Chèn nội dung

                    for (int j = 0; j < soLuong; j++)
                    {
                        var dtRow = danhSach[j];
                        row = sheet.CreateRow(j + 8);
                        cell = row.CreateCell(0);
                        cell.SetCellValue(j + 1);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        cell = row.CreateCell(1);
                        cell.SetCellValue(dtRow.MaSinhVien);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        cell = row.CreateCell(2);
                        cell.SetCellValue(dtRow.HoDem);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        cell = row.CreateCell(3);
                        cell.SetCellValue(dtRow.Ten);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        if (colHK1.Visible == true)
                        {
                            cell = row.CreateCell(4);
                            cell.SetCellValue(dtRow.HK1);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK2.Visible == true)
                        {
                            cell = row.CreateCell(5);
                            cell.SetCellValue(dtRow.HK2);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK3.Visible == true)
                        {
                            cell = row.CreateCell(6);
                            cell.SetCellValue(dtRow.HK3);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK4.Visible == true)
                        {
                            cell = row.CreateCell(7);
                            cell.SetCellValue(dtRow.HK4);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK5.Visible == true)
                        {
                            cell = row.CreateCell(8);
                            cell.SetCellValue(dtRow.HK5);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK6.Visible == true)
                        {
                            cell = row.CreateCell(9);
                            cell.SetCellValue(dtRow.HK6);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK7.Visible == true)
                        {
                            cell = row.CreateCell(10);
                            cell.SetCellValue(dtRow.HK7);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK8.Visible == true)
                        {
                            cell = row.CreateCell(11);
                            cell.SetCellValue(dtRow.HK8);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK9.Visible == true)
                        {
                            cell = row.CreateCell(12);
                            cell.SetCellValue(dtRow.HK9);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                        if (colHK10.Visible == true)
                        {
                            cell = row.CreateCell(13);
                            cell.SetCellValue(dtRow.HK10);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                    }
                    #endregion

                    #region Chèn phần bottom
                    indexRow = soLuong + 9;
                    row = sheet.GetRow(indexRow);
                    try
                    {
                        cell = row.CreateCell(4);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(4);
                    }
                    cell.SetCellValue(string.Format("Huế, ngày {0:dd} tháng {0:MM} năm {0:yyyy}", DateTime.Now));
                    cell.CellStyle = cellTongSoStyle;

                    indexRow++;
                    row = sheet.GetRow(indexRow);
                    try
                    {
                        cell = row.CreateCell(2);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(2);
                    }
                    cell.SetCellValue(string.Format("Kế toán trưởng"));
                    cell.CellStyle = titleCellStyle;
                    try
                    {
                        cell = row.CreateCell(4);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(4);
                    }
                    cell.SetCellValue(string.Format("Trưởng đơn vị"));
                    cell.CellStyle = titleCellStyle;

                    indexRow += 6;
                    row = sheet.GetRow(indexRow);
                    var thongtin = BizNguoiSuDung.ThongTinDangKy();
                    if (thongtin != null && thongtin.Length == 2)
                    {
                        try
                        {
                            cell = row.CreateCell(2);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(2);
                        }
                        cell.SetCellValue(thongtin[0]);
                        cell.CellStyle = cellBottomStyle;

                        try
                        {
                            cell = row.CreateCell(4);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(4);
                        }
                        cell.SetCellValue(thongtin[1]);
                        cell.CellStyle = cellBottomStyle;
                    }
                    #endregion

                    #region Lưu ra file Excel mới
                    sheet.ForceFormulaRecalculation = true;
                    if (!Directory.Exists(string.Format("{0}\\Temp", Application.StartupPath)))
                        Directory.CreateDirectory(string.Format("{0}\\Temp", Application.StartupPath));
                    var tenfile = string.Format("{0}\\Temp\\ThongKeLop_{2}_{1:dd_MM_yyyy_hh_mm_ss}.xls", Application.StartupPath, DateTime.Now, tenLopGoc);
                    try
                    {
                        using (var fileData = new FileStream(tenfile, FileMode.Create))
                        {
                            templateWorkbook.Write(fileData);
                            fileData.Close();

                            #region Excel commented
                            #endregion
                            System.Diagnostics.Process.Start(tenfile);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString());
                    }
                    fs.Close();
                    #endregion
                };
                #endregion

                #region On complete
                frm.RunWorkerCompleted += delegate(object oSender, RunWorkerCompletedEventArgs oE)
                {
                    if (oE.Error != null)
                    {
                        string msg = oE.Error.Message;
                        if (oE.Error.InnerException != null)
                            msg += oE.Error.InnerException.Message;
                        DialogUtils.ShowError(msg);
                    }

                };
                #endregion

                frm.StartWorking(this);
            }
        }

        private void rdHocPhi_CheckedChanged(object sender, EventArgs e)
        {
            LoadDanhSachThongKeLop();
        }
    }
}
